﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using ProjetoBe.cs;

namespace ProjetoDalc.cs
{
    public class DependenciaDalc
    {
        public void criarDependencia(DependenciaBe objeto)
        { 
            using(SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "INSERT INTO DEPENDENCIA VALUES(@idCrianca, @idResponsavel, @data, @identificacao, @flag);";
                command.Parameters.AddWithValue("idCrianca", objeto.id_crianca);
                command.Parameters.AddWithValue("idResponsavel", objeto.idResponsavel);
                command.Parameters.AddWithValue("data", objeto.data);
                command.Parameters.AddWithValue("identificacao", objeto.identificacao);
                command.Parameters.AddWithValue("flag","ATIVO");

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();

            }
        }

        public bool verificaDisponibilidade(int numeroDependencia)
        {
            bool verificador = false;
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                SqlDataReader reader;

                command.Connection = conexao;
                command.CommandText = "SELECT DEPENDENCIA_IDENTIFICACAO FROM DEPENDENCIA WHERE DEPENDENCIA_IDENTIFICACAO = @NUMERO ";
                command.Parameters.AddWithValue("NUMERO", numeroDependencia);
                conexao.Open();
                reader = command.ExecuteReader();
                

                if (!reader.HasRows)
                    verificador = true;

                conexao.Close();
            }
            return verificador;
        }

        public List<int> retornaIDsResponsaveisPorIDcrianca(int id)
        {
            List<int> idsResponsaveis = new List<int>();

            try
            {
                using(SqlConnection conexao = Connection.CriarConexao())
                {
                    SqlCommand command = new SqlCommand();
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    SqlDataReader reader;

                    command.Connection = conexao;
                    command.CommandText = "SELECT RESPONSAVEL_ID FROM DEPENDENCIA WHERE CRIANCA_ID = @ID;";
                    command.Parameters.AddWithValue("ID", id);
                    conexao.Open();
                    reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while(reader.Read())
                        {
                            idsResponsaveis.Add((reader.GetInt32(0)));
                        }
                    }
                    conexao.Close();
                }
            }
            catch{}

            return idsResponsaveis;
        }

        public ResponsavelBe buscarResponsaveisPorIDs(int ID)
        {
            SqlDataReader reader;
            ResponsavelBe responsavelBe = new ResponsavelBe();

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "SELECT * FROM RESPONSAVEL WHERE RESPONSAVEL_ID = @ID";
                command.Parameters.AddWithValue("ID", ID);
                conexao.Open();
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        responsavelBe.id = Convert.ToInt32(reader["RESPONSAVEL_ID"]);
                        responsavelBe.nome = reader["RESPONSAVEL_NOME"].ToString();
                        responsavelBe.nascimento = Convert.ToDateTime(reader["RESPONSAVEL_NASCIMENTO"]);
                        responsavelBe.parentesco = reader["RESPONSAVEL_PARENTESCO"].ToString();
                        responsavelBe.endereco.id = Convert.ToInt32(reader["ENDERECO_ID"]);
                        responsavelBe.data_cadastro = Convert.ToDateTime(reader["RESPONSAVEL_DATA_CADASTRO"]);
                        responsavelBe.cpf = reader["RESPONSAVEL_CPF"].ToString();
                    }
                }
                conexao.Close();
            }
            return responsavelBe; ;
        }

        public DependenciaBe buscarCriancaPelaIdentificacao(int identificacao)
        {
            SqlDataReader reader;
            DependenciaBe dependencia = new DependenciaBe();

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "SELECT * FROM DEPENDENCIA WHERE DEPENDENCIA_IDENTIFICACAO = @identificacao and DEPENDENCIA_FLAG = 'ATIVO';";
                command.Parameters.AddWithValue("@identificacao", identificacao);
              
                conexao.Open();
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        dependencia.id_crianca = Convert.ToInt16(reader["CRIANCA_ID"]);
                       
                    }
                }
                conexao.Close();
            }
            return dependencia;
        }

        public void deletarDependencia(int id)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "UPDATE DEPENDENCIA SET DEPENDENCIA_FLAG = 'DESATIVO' WHERE CRIANCA_ID = @id";
                command.Parameters.AddWithValue("@id", id);

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }

        public void incrementaCountCrianca(int id)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "UPDATE CRIANCA SET CRIANCA_COUNT = CRIANCA_COUNT + 1 WHERE CRIANCA_ID = @id";
                command.Parameters.AddWithValue("@id", id);

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }
    }
}
